package dbConnections;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import dbStruct.Column;
import dbStruct.Connector;
import dbStruct.Connectors;
import dbStruct.DatabaseStructure;
import dbStruct.Nodes;
import dbStruct.Table;
import dbStruct.Connector.Cardinality;
import dbStruct.Connector.RelationType;

public class MsAccessConnection extends DBConnection{
	private static MsAccessConnection instance;
	private DatabaseStructure dbStruct;
	private Nodes			  nodes;
	private Connectors		  connectors;
	
	public MsAccessConnection()
	{
		super(DBConnection.msAccess);
		dbStruct   = DatabaseStructure.getInstance();
		nodes      = dbStruct.getNodes();
		connectors = dbStruct.getConnectors(); 
	}
	
	public static MsAccessConnection getInstance()
	{
		if (instance==null) instance = new MsAccessConnection();
		return instance;
	}

	@Override
	protected void getRelations() {
		
		System.out.println("\nTOTALE TABELLE:" + dbStruct.getNodes().getTables().size() );
		System.out.println("  CREAZIONE DELLE RELAZIONI:");
		
		String qry = "select * from MSysRelationships";
		try {
			Statement st = super.connection.createStatement();
			ResultSet rs = st.executeQuery(qry);
			
			while(rs.next())
			{
				String pkColumnName = rs.getString("szReferencedColumn");
				String pkTableName  = rs.getString("szReferencedObject"); 
				String fkColumnName = rs.getString("szColumn");
				String fkTableName  = rs.getString("szObject");
				@SuppressWarnings("unused")
				int    grBit	  	= rs.getInt("grbit"); // Tipo di cascade in msAccess
				Cardinality card  	= Cardinality.NONE;
				
				String pkIndexName = pkTableName + "." + pkColumnName;
				String fkIndexName = fkTableName + "." + fkColumnName;
				
				System.out.println(pkIndexName + " <- " + fkIndexName);
				
				boolean pkIdx = ((Column) nodes.get(pkIndexName)).isPrimary();
				boolean fkIdx = ((Column) nodes.get(fkIndexName)).isPrimary();
				
				if( pkIdx &  fkIdx) card = Cardinality.ONE2ONE;
				if( pkIdx & !fkIdx) card = Cardinality.ONE2MANY;
				if(!pkIdx &  fkIdx) card = Cardinality.MANY2ONE;
				if(!pkIdx & !fkIdx) card = Cardinality.MANY2MANY;
				
				//Creo la relazione tra le due tabelle
				Table pkTable = (Table) nodes.get(pkTableName);
				Table fkTable = (Table) nodes.get(fkTableName);
				Connector conn = new Connector(pkTable, fkTable, RelationType.RELATION, card);
				connectors.add(conn); //Aggiungo la relazione alla collection CONNECTORS
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}	
	
	@Override
	protected void getTables()
	{
		Table	  table=null;
		ResultSet rs=null;
		
		try {
			rs = connection.getMetaData().getTables(null, null, null, null);
			
			while (rs.next())
			{
			  //String catalog = rs.getString("TABLE_CAT");
			  //String schema = rs.getString("TABLE_SCHEM");
			  String tableName = rs.getString("TABLE_NAME"); 
			  String tipo = rs.getString("TABLE_TYPE").replace(" ", "_");
			  if (tipo.equalsIgnoreCase("TABLE"))
			  {
				  table = new Table(tableName);
				  System.out.println("\n\n--CREATA TABELLA " + table.getName());
				  nodes.add(table); //Aggiungo la tabella alla collezione NODES
				  super.getColumns(table); //Leggo le colonne della tabella.
				  super.getIndexes(table); //Leggo gli indici della tabella.
			  }  
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
